﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using System.Data;
using bdxyit.com.RecruitmentAppSys.DBUtility;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：UserEducationModels
*系统时间：3/25/2012 7:16:18 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 7:16:18 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class UserEducationDAL : IUserEducationDAL
    {
        private const string SQL_SELECT_USEREDUCATION_BY_USERID = " select ID,StartTime,EndTime,SysID,UniversityID,PorID,DegreeID,DiplomaNum,EduID,EduNum,Flag,UserID,UniversityOther,PorOhter "
                                                                + " FROM UserEducations "
                                                                + " where UserID=@UserID";

        private const string SQL_DELETE_USEREDUCATION_BY_USERID = " delete from UserEducations "
                                                                + " where UserID=@UserID";

        private const string SQL_UPDATE_USEREDUCATION_BY_USERID = " update UserEducations set "
                                                                + " StartTime=@StartTime,"
                                                                + " EndTime=@EndTime,"
                                                                + " SysID=@SysID,"
                                                                + " UniversityID=@UniversityID,"
                                                                + " PorID=@PorID,"
                                                                + " DegreeID=@DegreeID,"
                                                                + " DiplomaNum=@DiplomaNum,"
                                                                + " EduID=@EduID,"
                                                                + " EduNum=@EduNum,"
                                                                + " Flag=@Flag,"
                                                                + " UserID=@UserID,"
                                                                + " UniversityOther=@UniversityOther,"
                                                                + " PorOhter=@PorOhter"
                                                                + " where UserID=@UserID";

        private const string SQL_INSERT_USEREDUCATION = " insert into UserEducations("
                                                      + " StartTime,EndTime,SysID,UniversityID,PorID,DegreeID,DiplomaNum,EduID,EduNum,Flag,UserID,UniversityOther,PorOhter)"
                                                      + " values ("
                                                      + " @StartTime,@EndTime,@SysID,@UniversityID,@PorID,@DegreeID,@DiplomaNum,@EduID,@EduNum,@Flag,@UserID,@UniversityOther,@PorOhter)";

        private const string SQL_SELECT_USEREDUCATION_BY_USERID_AND_FLAG = " select ID,StartTime,EndTime,SysID,UniversityID,PorID,DegreeID,DiplomaNum,EduID,EduNum,Flag,UserID,UniversityOther,PorOhter "
                                                                         + " FROM UserEducations "
                                                                         + " where UserID=@UserID and  Flag=@Flag";

        private const string SQL_DELETE_USEREDUCATION_BY_USERID_AND_FLAG = " delete from UserEducations "
                                                                         + " FROM UserEducations "
                                                                         + " where UserID=@UserID and  Flag=@Flag";




        private const string FILED_STARTTIME = "@StartTime";
        private const string FILED_ENDTIME = "@EndTime";
        private const string FILED_SYSID = "@SysId";
        private const string FIELD_UNIVERSITYID = "@UniversityID";
        private const string FIELD_PORID = "@PorID";
        private const string FIELD_DIPLOMANUM = "@DiplomaNum";
        private const string FIELD_EDUID = "@EduID";
        private const string FIELD_EDUNUM = "@EduNum";
        private const string FIELD_FALG = "@Flag";
        private const string FIELD_USERID = "@UserID";
        private const string FIELD_UNIVERSITYOTHER = "@UniversityOther";
        private const string FIELD_POROTHER = "@PorOhter";


        ///// <summary>
        ///// 获取所有的
        ///// </summary>
        ///// <returns></returns>
        public UserEducation SelectUserEducationByUserIdAndFlag(string userID, int flag)
        {
            UserEducation ue = null;
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 100), new SqlParameter(FIELD_FALG, SqlDbType.Int) };
            parm[0].Value = userID;
            parm[1].Value = flag;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_USEREDUCATION_BY_USERID_AND_FLAG, parm))
            {
                if (dr.Read())
                {
                    ue = new UserEducation();
                    if (dr.HasRows)
                    {
                        if (dr["ID"] != null && dr["ID"].ToString() != "")
                        {
                            ue.ID = int.Parse(dr["ID"].ToString());
                        }
                        if (dr["StartTime"] != null && dr["StartTime"].ToString() != "")
                        {
                            ue.StartTime = DateTime.Parse(dr["StartTime"].ToString());
                        }
                        if (dr["EndTime"] != null && dr["EndTime"].ToString() != "")
                        {
                            ue.EndTime = DateTime.Parse(dr["EndTime"].ToString());
                        }
                        if (dr["SysID"] != null && dr["SysID"].ToString() != "")
                        {
                            ue.SysId = dr["SysID"].ToString();
                        }
                        if (dr["UniversityID"] != null && dr["UniversityID"].ToString() != "")
                        {
                            ue.UniversityID = dr["UniversityID"].ToString();
                        }
                        if (dr["PorID"] != null && dr["PorID"].ToString() != "")
                        {
                            ue.PorID = dr["PorID"].ToString();
                        }
                        if (dr["DegreeID"] != null && dr["DegreeID"].ToString() != "")
                        {
                            ue.DegreeID = dr["DegreeID"].ToString();
                        }
                        if (dr["DiplomaNum"] != null && dr["DiplomaNum"].ToString() != "")
                        {
                            ue.DiplomaNum = dr["DiplomaNum"].ToString();
                        }
                        if (dr["EduID"] != null && dr["EduID"].ToString() != "")
                        {
                            ue.EduID = dr["EduID"].ToString();
                        }
                        if (dr["EduNum"] != null && dr["EduNum"].ToString() != "")
                        {
                            ue.EduNum = dr["EduNum"].ToString();
                        }
                        if (dr["Flag"] != null && dr["Flag"].ToString() != "")
                        {
                            ue.Flag = int.Parse(dr["Flag"].ToString());
                        }
                        if (dr["UserID"] != null && dr["UserID"].ToString() != "")
                        {
                            ue.UserID = dr["UserID"].ToString();
                        }
                        if (dr["UniversityOther"] != null && dr["UniversityOther"].ToString() != "")
                        {
                            ue.UniversityOther = dr["UniversityOther"].ToString();
                        }
                        if (dr["PorOhter"] != null && dr["PorOhter"].ToString() != "")
                        {
                            ue.PorOhter = dr["PorOhter"].ToString();
                        }
                    }
                    return ue;
                }
            }
            return ue;
        }

        ///// <summary>
        ///// 获取
        ///// </summary>
        ///// <returns></returns>
        public List<UserEducation> SelectUserEducationByUserId(string userId)
        {
            List<UserEducation> uel = new List<UserEducation>();
            SqlParameter parm = new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 100);
            parm.Value = userId;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_USEREDUCATION_BY_USERID_AND_FLAG, parm))
            {
                if (dr.Read())
                {
                    UserEducation ue = new UserEducation();
                    if (dr.HasRows)
                    {
                        if (dr["ID"] != null && dr["ID"].ToString() != "")
                        {
                            ue.ID = int.Parse(dr["ID"].ToString());
                        }
                        if (dr["StartTime"] != null && dr["StartTime"].ToString() != "")
                        {
                            ue.StartTime = DateTime.Parse(dr["StartTime"].ToString());
                        }
                        if (dr["EndTime"] != null && dr["EndTime"].ToString() != "")
                        {
                            ue.EndTime = DateTime.Parse(dr["EndTime"].ToString());
                        }
                        if (dr["SysID"] != null && dr["SysID"].ToString() != "")
                        {
                            ue.SysId = dr["SysID"].ToString();
                        }
                        if (dr["UniversityID"] != null && dr["UniversityID"].ToString() != "")
                        {
                            ue.UniversityID = dr["UniversityID"].ToString();
                        }
                        if (dr["PorID"] != null && dr["PorID"].ToString() != "")
                        {
                            ue.PorID = dr["PorID"].ToString();
                        }
                        if (dr["DegreeID"] != null && dr["DegreeID"].ToString() != "")
                        {
                            ue.DegreeID = dr["DegreeID"].ToString();
                        }
                        if (dr["DiplomaNum"] != null && dr["DiplomaNum"].ToString() != "")
                        {
                            ue.DiplomaNum = dr["DiplomaNum"].ToString();
                        }
                        if (dr["EduID"] != null && dr["EduID"].ToString() != "")
                        {
                            ue.EduID = dr["EduID"].ToString();
                        }
                        if (dr["EduNum"] != null && dr["EduNum"].ToString() != "")
                        {
                            ue.EduNum = dr["EduNum"].ToString();
                        }
                        if (dr["Flag"] != null && dr["Flag"].ToString() != "")
                        {
                            ue.Flag = int.Parse(dr["Flag"].ToString());
                        }
                        if (dr["UserID"] != null && dr["UserID"].ToString() != "")
                        {
                            ue.UserID = dr["UserID"].ToString();
                        }
                        if (dr["UniversityOther"] != null && dr["UniversityOther"].ToString() != "")
                        {
                            ue.UniversityOther = dr["UniversityOther"].ToString();
                        }
                        if (dr["PorOhter"] != null && dr["PorOhter"].ToString() != "")
                        {
                            ue.PorOhter = dr["PorOhter"].ToString();
                        }
                    }
                    uel.Add(ue);
                }
            }

            return uel;
        }

        /// <summary>
        /// 删除用户教育信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public bool DeleteUserEducationByUserId(string userID)
        {
            bool flag = false;
            SqlParameter parm = new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 100);
            parm.Value = userID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_USEREDUCATION_BY_USERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                flag = true;
                cmd.Parameters.Clear();
            }
            return flag;
        }

        /// <summary>
        /// 删除用户教育信息
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public bool DeleteUserEducationByUserIdAndFlag(string userID, int flagu)
        {
            bool flag = false;
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 100), new SqlParameter(FIELD_FALG, SqlDbType.Int) };
            parm[0].Value = userID;
            parm[1].Value = flagu;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_USEREDUCATION_BY_USERID_AND_FLAG;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                flag = true;
                cmd.Parameters.Clear();
            }
            return flag;
        }

        /// <summary>
        /// 插入用户教育信息
        /// </summary>
        /// <param name="ue"></param>
        /// <returns></returns>
        public bool InsertUserEducationByUserId(UserEducation ue)
        {
            bool flag = false;
            SqlParameter[] parm = GetUserEducationParameters();
            parm[0].Value = ue.StartTime;
            parm[1].Value = ue.EndTime;
            parm[2].Value = ue.SysId;
            parm[3].Value = ue.UniversityID;
            parm[4].Value = ue.PorID;
            parm[5].Value = ue.DegreeID;
            parm[6].Value = ue.DiplomaNum;
            parm[7].Value = ue.EduID;
            parm[8].Value = ue.EduNum;
            parm[9].Value = ue.Flag;
            parm[10].Value = ue.UserID;
            parm[11].Value = ue.UniversityOther;
            parm[12].Value = ue.PorOhter;

            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_USEREDUCATION;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                flag = true;
                cmd.Parameters.Clear();
            }
            return flag;
        }

        /// <summary>
        /// 编辑用户教育信息
        /// </summary>
        /// <param name="ue"></param>
        /// <returns></returns>
        public bool EditUserEducationByUserId(UserEducation ue)
        {
            bool flag = false;
            SqlParameter[] parm = GetUserEducationParameters();
            parm[0].Value = ue.StartTime;
            parm[1].Value = ue.EndTime;
            parm[2].Value = ue.SysId;
            parm[3].Value = ue.UniversityID;
            parm[4].Value = ue.PorID;
            parm[5].Value = ue.DegreeID;
            parm[6].Value = ue.DiplomaNum;
            parm[7].Value = ue.EduID;
            parm[8].Value = ue.EduNum;
            parm[9].Value = ue.Flag;
            parm[10].Value = ue.UserID;
            parm[11].Value = ue.UniversityOther;
            parm[12].Value = ue.PorOhter;

            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_USEREDUCATION_BY_USERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                flag = true;
                cmd.Parameters.Clear();
            }
            return flag;
        }

        /// <summary>
        /// 获取用户教育信息参数
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] GetUserEducationParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
					new SqlParameter(FILED_STARTTIME, SqlDbType.DateTime),
					new SqlParameter(FILED_ENDTIME, SqlDbType.DateTime),
					new SqlParameter(FILED_SYSID, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_UNIVERSITYID, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_PORID, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_DIPLOMANUM, SqlDbType.VarChar, 120),
                    new SqlParameter(FIELD_EDUID, SqlDbType.VarChar, 30),
					new SqlParameter(FIELD_EDUNUM, SqlDbType.VarChar, 128),
					new SqlParameter(FIELD_FALG, SqlDbType.Int),
					new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_UNIVERSITYOTHER, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_POROTHER, SqlDbType.VarChar, 120)
            };
            return parms;
        }
    }
}